package net.wanho.customerManage.dao.impl;

import net.wanho.commons.exception.DaoException;
import net.wanho.commons.exception.DataAccessException;
import net.wanho.commons.factory.BeanFactory;
import net.wanho.commons.util.JDBCTemplate;
import net.wanho.customerManage.dao.CustomerDao;
import net.wanho.customerManage.po.Customer;
import net.wanho.customerManage.query.CustomerQuery;
import net.wanho.customerManage.vo.CustomerVo;
import org.apache.commons.lang3.ArrayUtils;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.List;

public class CustomerDaoImpl implements CustomerDao {
    private JDBCTemplate jdbcTemplate = BeanFactory.getBean(JDBCTemplate.class) ;
    @Override
    public List<CustomerVo> selectAllCustomer() throws DaoException {
        final String SELECT_CUSTOMER_ALL_SQL = "SELECT  c.CUSTOMER_ID CUSTOMERID, \n" +
                "\tc.CUSTOMER_NAME CUSTOMERNAME, \n" +
                "\tc.POST_CODE POSTCODE, \n" +
                "\tc.FIELD_ID FIELDID, \n" +
                "\tc.SOURCE_ID SOURCEID, \n" +
                "\tc.CREATE_TIME CREATETIME, \n" +
                "\tc.UPDATE_TIME UPDATETIME, \n" +
                "\tc.EMPLOYEE_NUMBERS EMPLOYEENUMBERS, \n" +
                "\tc.PRINPICAL PRINPICAL, \n" +
                "\tc.ADDRESS ADDRESS, \n" +
                "\tc.TAG TAG, \n" +
                "\tc.REMARKS REMARKS, \n" +
                "\tc.BUSUBESS_VOLUME BUSUBESSVOLUME ,\n" +
                "\tl.NAME linkManName,\n" +
                "\tw.FIELD_NAME FIELDNAME,\n" +
                "\tb.SOURCE_NAME SOURCENAME\n" +
                "FROM customer c\n" +
                "  LEFT JOIN link_man l\n" +
                "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                "  LEFT JOIN working_field w\n" +
                "    ON c.FIELD_ID = w.FIELD_ID\n" +
                "  LEFT JOIN business_source b\n" +
                "    ON c.SOURCE_ID = b.SOURCE_ID" ;
        try {
            return jdbcTemplate.queryForList(SELECT_CUSTOMER_ALL_SQL,CustomerVo.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DaoException(e.getMessage()) ;
        }
    }

    @Override
    public List<CustomerVo> selectByCondCustomer(CustomerQuery customerQuery) throws DaoException {
        final String SELECT_CUSTOMER_BY_COND_SQL = "SELECT  c.CUSTOMER_ID CUSTOMERID, \n" +
                "\tc.CUSTOMER_NAME CUSTOMERNAME, \n" +
                "\tc.POST_CODE POSTCODE, \n" +
                "\tc.FIELD_ID FIELDID, \n" +
                "\tc.SOURCE_ID SOURCEID, \n" +
                "\tc.CREATE_TIME CREATETIME, \n" +
                "\tc.UPDATE_TIME UPDATETIME, \n" +
                "\tc.EMPLOYEE_NUMBERS EMPLOYEENUMBERS, \n" +
                "\tc.PRINPICAL PRINPICAL, \n" +
                "\tc.ADDRESS ADDRESS, \n" +
                "\tc.TAG TAG, \n" +
                "\tc.REMARKS REMARKS, \n" +
                "\tc.BUSUBESS_VOLUME BUSUBESSVOLUME ,\n" +
                "\tl.NAME linkManName,\n" +
                "\tw.FIELD_NAME FIELDNAME,\n" +
                "\tb.SOURCE_NAME SOURCENAME\n" +
                "FROM customer c\n" +
                "  LEFT JOIN link_man l\n" +
                "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                "  LEFT JOIN working_field w\n" +
                "    ON c.FIELD_ID = w.FIELD_ID\n" +
                "  LEFT JOIN business_source b\n" +
                "    ON c.SOURCE_ID = b.SOURCE_ID where 1=1 " ;
        StringBuffer sb = new StringBuffer(SELECT_CUSTOMER_BY_COND_SQL);
        Object[] params = ArrayUtils.EMPTY_OBJECT_ARRAY ;
        //根据客户名称模糊查询  text=aaa&type=1
            if (customerQuery.getType().equals(1)){
                sb.append(" and  c.CUSTOMER_NAME like ? ") ;
                params = ArrayUtils.add(params,"%"+customerQuery.getText()+"%") ;
            }
        //根据所属行业 id 精确查询 text=6&type=2
        if (customerQuery.getType().equals(2)){
            sb.append(" and c.FIELD_ID = ?") ;
            params = ArrayUtils.add(params,customerQuery.getText()) ;
        }
        //根据客户来源 精确查询  text=1003&type=3
        if (customerQuery.getType().equals(3)){
            sb.append(" and c.SOURCE_ID = ? ") ;
            params = ArrayUtils.add(params,customerQuery.getText()) ;
        }
        //根据创建时间 范围查询 text=2021-05-14&type=5&texTime=2021-05-29
        if (customerQuery.getType().equals(5)){
            sb.append(" and date(c.CREATE_TIME) between ? and  ? ") ;
            params = ArrayUtils.add(params,customerQuery.getText()) ;
            params = ArrayUtils.add(params,customerQuery.getTexTime()) ;
        }
        //根据修改时间 范围查询 text=2021-05-07&type=6&texTime=2021-05-15
        if (customerQuery.getType().equals(6)){
            sb.append(" and date(c.UPDATE_TIME) between ? and  ? ") ;
            params = ArrayUtils.add(params,customerQuery.getText()) ;
            params = ArrayUtils.add(params,customerQuery.getTexTime()) ;
        }
        try {
            return jdbcTemplate.queryForList(sb.toString(),CustomerVo.class,params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DaoException(e.getMessage()) ;
        }
    }

    @Override
    public CustomerVo selectByIdCustomer(Integer customerId) throws DaoException {
        final String SELECT_CUSTOMER_BY_ID_SQL = "SELECT  c.CUSTOMER_ID CUSTOMERID, \n" +
                "\tc.CUSTOMER_NAME CUSTOMERNAME, \n" +
                "\tc.POST_CODE POSTCODE, \n" +
                "\tc.FIELD_ID FIELDID, \n" +
                "\tc.SOURCE_ID SOURCEID, \n" +
                "\tc.CREATE_TIME CREATETIME, \n" +
                "\tc.UPDATE_TIME UPDATETIME, \n" +
                "\tc.EMPLOYEE_NUMBERS EMPLOYEENUMBERS, \n" +
                "\tc.PRINPICAL PRINPICAL, \n" +
                "\tc.ADDRESS ADDRESS, \n" +
                "\tc.TAG TAG, \n" +
                "\tc.REMARKS REMARKS, \n" +
                "\tc.BUSUBESS_VOLUME BUSUBESSVOLUME ,\n" +
                "\tl.NAME linkManName,\n" +
                "\tw.FIELD_NAME FIELDNAME,\n" +
                "\tb.SOURCE_NAME SOURCENAME\n" +
                "FROM customer c\n" +
                "  LEFT JOIN link_man l\n" +
                "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                "  LEFT JOIN working_field w\n" +
                "    ON c.FIELD_ID = w.FIELD_ID\n" +
                "  LEFT JOIN business_source b\n" +
                "    ON c.SOURCE_ID = b.SOURCE_ID  " +
                "  WHERE\n" +
                "  c.CUSTOMER_ID = ?" ;
        try {
            return jdbcTemplate.queryForObject(SELECT_CUSTOMER_BY_ID_SQL,CustomerVo.class,customerId);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DaoException(e.getMessage()) ;
        }
    }

    @Override
    public Integer insertCustomer(Customer customer) throws DaoException {
        final String INSERT_CUSTOMER_SQL = "insert  into customer(CUSTOMER_ID,CUSTOMER_NAME,POST_CODE,FIELD_ID,SOURCE_ID,CREATE_TIME," +
                "UPDATE_TIME,EMPLOYEE_NUMBERS,PRINPICAL,ADDRESS,TAG,REMARKS,BUSUBESS_VOLUME) values (?,?,?,?,?,?,?,?,?,?,?,?,?)" ;
        Object[] params = {customer.getCustomerId(),customer.getCustomerName(),
                customer.getPostCode(),customer.getFieldId(),customer.getSourceId(),customer.getCreateTime(),
                customer.getUpdateTime(),customer.getEmployeeNumbers(),customer.getPrinpical(),customer.getAddress(),
                customer.getTag(),customer.getRemarks(),customer.getBusubessVolume()} ;
        try {
            return jdbcTemplate.save(INSERT_CUSTOMER_SQL,params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DaoException(e.getMessage()) ;
        }
    }
}
